package com.ruoyi.common.utils;


import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson2.JSONObject;

import com.ruoyi.common.annotation.ExcelRow;
import lombok.Data;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.*;

@Component
public class ExcelMapUtils {


    /**
     * 多行表头导出
     * @param title 文件名
     * @param headerList  表头合并信息（重点!） 以一个字符串举例 "C2,D2,收费业务" 三部分之间 “,” 分隔！！！
     *                    C2,D2:需要合并的单元格  收费业务：合并之后的单元格需要填充的内容
     * @param exportExcelBeans  导出数据（记得在需要导出的字段上加 {@link ExcelRow} 注解）
     * @param dataRowStartIndex  数据起始行（从0开始）
     * @param clazz 导出数据类型 Class
     * @throws IOException
     */

    public <T> JSONObject exportExcelListMultiHeader(String title, List<String> headerList, Integer dataRowStartIndex, List<T> exportExcelBeans, Class<T> clazz, HttpServletRequest request) throws IOException, InvocationTargetException, IllegalAccessException, NoSuchMethodException {
        JSONObject result = new JSONObject();
        //创建workBook
        HSSFWorkbook wb = createWorkBook(title,headerList,dataRowStartIndex,exportExcelBeans,clazz);
        String path = request.getSession().getServletContext().getRealPath("/") + "export/";
        File file = new File(path);
        if (!file.exists()) {
            file.mkdirs();
        }
        Date now = new Date();

        String nowStr = DateUtil.format(now,"yyyyMMddHHmmss");
        String fileName = title + nowStr + ".xls";
        String fullName = path + fileName;
        FileOutputStream out = null;
        try {
            out = new FileOutputStream(fullName);
            wb.write(out);
            out.flush();
            out.close();
        } catch (Exception e) {
            result.put("error", e.getMessage());
            try {
                out.flush();
                out.close();
            } catch (IOException e1) {
            }
        }
        result.put("result", 1);
        result.put("fileName", fileName);
        return result;
    }

    public <T> void exportExcelListMultiHeader(String title, List<String> headerList, Integer dataRowStartIndex, List<T> exportExcelBeans, Class<T> clazz, HttpServletResponse response) throws IOException, InvocationTargetException, IllegalAccessException, NoSuchMethodException {
        //创建workBook
        HSSFWorkbook wb = createWorkBook(title,headerList,dataRowStartIndex,exportExcelBeans,clazz);
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        try
        {
            wb.write(response.getOutputStream());
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            IOUtils.closeQuietly(wb);
        }
    }
    private <T> HSSFWorkbook createWorkBook(String title,List<String> headerList, Integer dataRowStartIndex, List<T> exportExcelBeans, Class<T> clazz) throws InvocationTargetException, IllegalAccessException, NoSuchMethodException {
        //创建Excel
        HSSFWorkbook wb = new HSSFWorkbook();
        //创建sheet
        HSSFSheet sheet = wb.createSheet(title);
        //标题行高度
        setTitleRowHeight(sheet);
        //标题样式
        HSSFCellStyle titleStyle = createTitleStyle(wb);
        //表头样式
        HSSFCellStyle headerStyle = createHeaderStyle(wb);
        //数据格式
        HSSFCellStyle dataStyle = createDataStyle(wb);
        //合并标题、表头单元格 并 填入内容(以及格式)
        mergeHeader(sheet,headerList,headerStyle,titleStyle);
        //创建 列号-属性信息的Map
        Map<Integer,FieldInfo> fieldInfoMap= createRowIndexFieldInfoMap(clazz);
        //写入数据
        for (int i = 0; i < exportExcelBeans.size(); i++) {
            for (int j = 0; j < fieldInfoMap.size(); j++) {
                //反射优化 从map中取值
                FieldInfo fieldInfo = fieldInfoMap.get(j);
                Method method = fieldInfo.getMethod();
                Object value = method.invoke(exportExcelBeans.get(i));
                String outPutVal=changeToOutPutVal(value,fieldInfo.getType());
                writeCellVal(i+dataRowStartIndex,j,outPutVal,dataStyle,sheet);
            }
        }
        //自动整理列宽
        for (int i = 0; i < fieldInfoMap.size(); i++) {
            sheet.autoSizeColumn(i, true);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 16 / 10);
        }
        return wb;
    }

    private HSSFCellStyle createTitleStyle(HSSFWorkbook wb) {

        HSSFCellStyle titleStyle = wb.createCellStyle();
        HSSFFont titleFont = wb.createFont();
        titleFont.setBold(true);//粗体
        titleFont.setFontHeightInPoints((short) 15);
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        return titleStyle;
    }

    private HSSFCellStyle createHeaderStyle(HSSFWorkbook wb) {
        HSSFCellStyle headStyle = wb.createCellStyle();
        HSSFFont headFont = wb.createFont();
        headFont.setBold(true);//粗体
        headFont.setFontHeightInPoints((short) 12);
        headStyle.setFont(headFont);
        headStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        headStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        return headStyle;
    }

    private HSSFCellStyle createDataStyle(HSSFWorkbook wb) {
        HSSFCellStyle textStyle = wb.createCellStyle();
        textStyle.setWrapText(true);
        textStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        textStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        return textStyle;
    }

    private void mergeHeader(HSSFSheet sheet, List<String> headerList, HSSFCellStyle headerStyle, HSSFCellStyle titleStyle) {
        for (String s : headerList) {
            String[] arr = s.split(",");
            if(arr[0].equals(arr[1])){
                //单个单元格 直接写入值(不合并了)
                int[] start = convertToRowCol(arr[0]);
                writeCellVal(start[0],start[1],arr[2], headerStyle, sheet);
            }else {
                //多单元格合并
                int[] start = convertToRowCol(arr[0]);
                int[] end = convertToRowCol(arr[1]);
                int startRow = Math.min(start[0], end[0]);
                int endRow = Math.max(start[0], end[0]);
                int startCol = Math.min(start[1], end[1]);
                int endCol = Math.max(start[1], end[1]);
                sheet.addMergedRegion(new CellRangeAddress(startRow, endRow, startCol,endCol));
                //起始行为0,起始列为0 即为标题行 用标题样式 其他用表头样式
                writeCellVal(startRow,startCol,arr[2],startRow==0&&startCol==0?titleStyle:headerStyle,sheet);
            }
        }
    }

    private <T> Map<Integer, FieldInfo> createRowIndexFieldInfoMap(Class<T> clazz) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        Map<Integer,FieldInfo> map=new HashMap<>();
        for (Field field : clazz.getDeclaredFields()) {
            //属性上有@ExcelRow注解
            if(field.isAnnotationPresent(ExcelRow.class)){
                FieldInfo info = new FieldInfo();
                info.setFieldName(field.getName());
                info.setMethod(getMethodByFieldName(clazz,field.getName()));
                info.setType(getAnnotationValue(field,ExcelRow.class,"type"));
                map.put(getAnnotationValue(field,ExcelRow.class,"rowIndex"),info);
            }
        }
        return map;
    }
    private String changeToOutPutVal(Object value, int type) {
        switch (type){
            case 0:return Objects.isNull(value)?"":value.toString();
            case 1:return Objects.isNull(value)?"0":value.toString();
            case 2:return Objects.isNull(value)?"0.00":value.toString();
            default:return "";
        }
    }

    private void writeCellVal(int row, int col, String val, HSSFCellStyle style, HSSFSheet sheet) {
        HSSFRow sheetRow = sheet.getRow(row)==null?sheet.createRow(row):sheet.getRow(row);
        HSSFCell cell = sheetRow.getCell(col) == null ? sheetRow.createCell(col) : sheetRow.getCell(col);
        cell.setCellValue(val);
        cell.setCellStyle(style);
    }

    @Data
    class FieldInfo{
        private String fieldName;
        private Method method;
        private int type;
    }

    private void setTitleRowHeight(HSSFSheet sheet) {
        HSSFRow titleRow = sheet.getRow(0)==null?sheet.createRow(0):sheet.getRow(0);
        titleRow.setHeightInPoints(40);
    }

    private <T> T getAnnotationValue(Field field, Class annotationType, String propertyName) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        Annotation annotation = field.getAnnotation(annotationType);
        Method method = annotation.getClass().getMethod(propertyName);
        Object val = method.invoke(annotation);
        return (T) val;
    }

    private static Method getMethodByFieldName(Class clazz,String fieldName) throws NoSuchMethodException {
        return clazz.getMethod(toGetMethodName(fieldName));
    }
    public static String toGetMethodName(String str) {
        if (str == null || str.isEmpty()) {
            return str;
        }
        char firstChar = str.charAt(0);
        char capitalizedChar = Character.toUpperCase(firstChar);
        String rest = str.length() > 1 ? str.substring(1) : "";
        return "get"+capitalizedChar + rest;
    }

    private  int[] convertToRowCol(String coordinate) {
        int[] rowCol = new int[2];

        // 将字母列数转化为数字
        String colStr = coordinate.replaceAll("[^a-zA-Z]", "").toUpperCase();
        int col = 0;
        for (int i = colStr.length() - 1; i >= 0; i--) {
            char c = colStr.charAt(i);
            col += (c - 'A' + 1) * Math.pow(26, colStr.length() - 1 - i);
        }

        // 解析行数
        int row = Integer.parseInt(coordinate.replaceAll("[^0-9]", ""));

        rowCol[0] = row-1;
        rowCol[1] = col-1;

        return rowCol;
    }


}
